﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GenUtil.Util;
using System.Data.Common;
using GenUtil.Dto;
using System.Data;

namespace DataLayer.Dao.Sql
{
    public class StudentDaoSql : BasicDaoSql, StudentDao
    {
        private static Logger logger = new Logger(typeof(StudentDaoSql));

        private static string SQL_INSERT_STUDENT = @" INSERT INTO " +SCHEMA + @".STUDENT (STUDENT_ID,FIRST_NAME,LAST_NAME,PASSWORD,DEPARTMENT,
        ROLL_NO,ADDRESS_NO1,ADDRESS_NO2,EMAIL_ID,LAST_UPD_TS,LAST_UPD_USERID) 
        VALUES (@STUDENT_ID,@FIRST_NAME,@LAST_NAME,@PASSWORD,@DEPARTMENT,@ROLL_NO,@ADDRESS_NO1,@ADDRESS_NO2,@EMAIL_ID,@LAST_UPD_TS,@LAST_UPD_USERID) ";
        public void InsertStudentDto(StudentDto studentDto)
        {
            logger.debug("Entering StudentDaoSql.InsertStudentDto()");
            DbConnection con = null;
            DbCommand com = null;
            try
            {
                con = getDatabaseConnection();
                com = CreateDBCommand(con, SQL_INSERT_STUDENT);
                setStatement(null, com, studentDto);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                logger.fatal("Exception in StudentDaoSql.InsertStudentDto():" + e.Message, e);
                throw new DaoException("Exception in StudentDaoSql.InsertStudentDto(): " + e.Message, e);
            }
            finally
            {
                if (com != null) com.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting CustomerDaoSql.InsertCustomerDto()");
        }
        private void setStatement(String methodName, DbCommand com, StudentDto studentDto)
        {

            DbParameter[] chkPara = CreateDBParameter(com, 11);

            if (methodName == null)
            {
                PopulateDBParameter(chkPara[0], "STUDENT_ID", studentDto.StudentId);
                PopulateDBParameter(chkPara[1], "FIRST_NAME", studentDto.FirstName);
                PopulateDBParameter(chkPara[2], "LAST_NAME", studentDto.LastName);
                PopulateDBParameter(chkPara[3], "PASSWORD", studentDto.Password);
                PopulateDBParameter(chkPara[4], "DEPARTMENT", studentDto.Department);
                PopulateDBParameter(chkPara[5], "ROLL_NO", studentDto.RollNo);
                PopulateDBParameter(chkPara[6], "ADDRESS_NO1", studentDto.AddressNo1);
                PopulateDBParameter(chkPara[7], "ADDRESS_NO2", studentDto.AddressNo2);
                PopulateDBParameter(chkPara[8], "EMAIL_ID", studentDto.EmailId);
                PopulateDBParameter(chkPara[9], "LAST_UPD_TS", studentDto.LastUpdDts);
                PopulateDBParameter(chkPara[10], "LAST_UPD_USERID", studentDto.LastupdUserId);

                com.Parameters.AddRange(chkPara);

            }
        }
        private static string SQL_GET_STUDENT = @"SELECT * FROM STUDENT where STUDENT_ID = @STUDENT_ID";

        public StudentDto getStudentDto(int studentId)
        {
            logger.debug("Entering StudentDaoSql.getStudentDto()");

            StudentDto studentDto = null;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            Dictionary<string, object> stringofpara = new Dictionary<string, object>();
            stringofpara.Add("studentId", studentId);

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_GET_STUDENT);
                DbParameter[] para = CreateDBParameter(pstmt, 1);
                PopulateDBParameter(para[0], "STUDENT_ID", studentId);
                pstmt.Parameters.AddRange(para);
                rs = pstmt.ExecuteReader();

                if (rs.Read())
                {
                    studentDto = rsToDto(rs);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in studentDaoSql.getstudentDto():" + e.Message, e);
                throw new Exception("Exception : studentDaoSql.getstudentDto() : " + e.Message, e);
            }
            finally
            {
                if (rs != null) rs.Close();
                if (pstmt != null) pstmt.Dispose();
                if (con != null) con.Close();
            }
            logger.debug("exiting ODWebServiceDaoDb2sp.getPermitsForPermitNumber()");
            return studentDto;
        }

        private static string SQL_GET_MAX_STUDENT_ID = @"SELECT Max(STUDENT_ID) FROM STUDENT";

        public int getMaxStudentId()
        {
            logger.debug("Entering StudentDaoSql.getMaxStudentId()");

            int maxStudentId = 0;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_GET_MAX_STUDENT_ID);
                rs = pstmt.ExecuteReader();
                if (rs.Read())
                {
                    maxStudentId = rs[0] == DBNull.Value ? 0 : Convert.ToInt32(rs[0]);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in StudentDaoSql.getMaxStudentId():" + e.Message, e);
                throw new Exception("Exception : StudentDaoSql.getMaxStudentId() : " + e.Message, e);
            }
            finally
            {
                if (rs != null) rs.Close();
                if (pstmt != null) pstmt.Dispose();
                if (con != null) con.Close();
            }
            logger.debug("exiting StudentDaoSql.getMaxStudentId()");
            return maxStudentId;
        }
        private StudentDto rsToDto(DbDataReader rs)
        {
            StudentDto studentDto = null;

            if (rs != null)
            {
                studentDto = new StudentDto();
                studentDto.setStudentId(getIntValue(rs, "STUDENT_ID"));
                studentDto.setFirstName(getStringValue(rs, "FIRST_NAME"));
                studentDto.setLastName(getStringValue(rs, "LAST_NAME"));
                studentDto.setPassword(getStringValue(rs, "PASSWORD"));
                studentDto.setDepartment(getStringValue(rs, "DEPARTMENT"));
                studentDto.setRollNo(getStringValue(rs, "ROLL_NO"));
                studentDto.setAddressNo1(getStringValue(rs, "ADDRESS_NO1"));
                studentDto.setAddressNo2(getStringValue(rs, "ADDRESS_NO2"));
                studentDto.setEmailId(getStringValue(rs, "EMAIL_ID"));
                studentDto.setLastUpdDts(getDateTimeValue(rs, "LAST_UPD_TS"));
                studentDto.setLastupdUserId(getStringValue(rs, "LAST_UPD_USERID"));
            }
            return studentDto;
        }

        private static string SQL_SELECT_STUDENT = @"SELECT * FROM STUDENT ORDER BY LAST_UPD_TS DESC";

        public List<StudentDto> getStudentDtos()
        {
            logger.debug("Entering StudentDaoSql.getStudentDto()");

            List<StudentDto> studentDtos = new List<StudentDto>();

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            StudentDto studentDto = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_SELECT_STUDENT);
                rs = pstmt.ExecuteReader();

                while (rs.Read())
                {
                    studentDto = rsToDto(rs);
                    studentDtos.Add(studentDto);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in StudentDaoSql.getMaxStudentId():" + e.Message, e);
                throw new Exception("Exception : StudentDaoSql.getMaxStudentId() : " + e.Message, e);
            }
            finally
            {
                if (pstmt != null) pstmt.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting StudentDaoSql.getMaxStudentId()");
            return studentDtos;
        }

        private static string SQL_FETCH_RECORD = @"SELECT * FROM STUDENT WHERE ROLL_NO = @ROLL_NO";

        public StudentDto getStudentRecord(string rollNo)
        {
            logger.debug("Entering StudentDaoSql.getStudentDto()");

            StudentDto studentDto = null;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_FETCH_RECORD);
                DbParameter[] para = CreateDBParameter(pstmt, 1);
                PopulateDBParameter(para[0], "ROLL_NO", rollNo);
                
                pstmt.Parameters.AddRange(para);

                rs = pstmt.ExecuteReader();

                if (rs.Read())
                {
                    studentDto = rsToDto(rs);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in StudentDaoSql.getMaxStudentId():" + e.Message, e);
                throw new Exception("Exception : StudentDaoSql.getMaxStudentId() : " + e.Message, e);
            }
            finally
            {
                if (pstmt != null) pstmt.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting StudentDaoSql.getMaxStudentId()");
            return studentDto;
        }
        private static string SQL_FETCH_EMAIL_ID = @"SELECT * FROM STUDENT WHERE EMAIL_ID = @EMAIL_ID";

        public StudentDto getStudentEmailId(string emailId)
        {
            logger.debug("Entering StudentDaoSql.getStudentDto()");

            StudentDto studentDto = null;

            DbConnection con = null;
            DbCommand pstmt = null;
            DbDataReader rs = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_FETCH_EMAIL_ID);
                DbParameter[] para = CreateDBParameter(pstmt, 1);
                PopulateDBParameter(para[0], "EMAIL_ID", emailId);

                pstmt.Parameters.AddRange(para);

                rs = pstmt.ExecuteReader();

                if (rs.Read())
                {
                    studentDto = rsToDto(rs);
                }
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in StudentDaoSql.getMaxStudentId():" + e.Message, e);
                throw new Exception("Exception : StudentDaoSql.getMaxStudentId() : " + e.Message, e);
            }
            finally
            {
                if (pstmt != null) pstmt.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting StudentDaoSql.getMaxStudentId()");
            return studentDto;
        }
        private static string SQL_SELECT_DATASET = @"SELECT * FROM STUDENT";
        public DataSet getDataSet()
        {
            logger.debug("Entering StudentDaoSql.getStudentDto()");

            DataSet ds = new DataSet();
            DbConnection con = null;
            DbCommand pstmt = null;

            try
            {
                con = getDatabaseConnection();
                pstmt = CreateDBCommand(con, SQL_SELECT_DATASET);
                DbDataAdapter adapt = null;
               
                adapt = DbProviderFactories.GetFactory("System.Data.SqlClient").CreateDataAdapter();
                adapt.SelectCommand = pstmt;
               
                adapt.Fill(ds);
            }
            catch (Exception e)
            {
                logger.fatal("Exception occured in StudentDaoSql.getMaxStudentId():" + e.Message, e);
                throw new Exception("Exception : StudentDaoSql.getMaxStudentId() : " + e.Message, e);
            }
            finally
            {
                if (pstmt != null) pstmt.Cancel();
                if (con != null) con.Close();
            }
            logger.debug("Exiting StudentDaoSql.getMaxStudentId()");
            return ds;
        }
    }
}
